﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Windows.Forms;
using BibliotecaProjeto.usuario;

namespace BibliotecaProjeto.persistencia
{
    public class ConexaoOdbc
    {
        public OdbcConnection DbConnection;
        private string nomeConexao = @"Driver={SQL Server};Server=MARCUS-NOTEBOOK\SQLEXPRESS;Initial Catalog=bancoProjeto;User Id=Marcus-Notebook\Marcus;Password=";
        #region abrir conexao
        public void abrirConexao()
        {
            this.DbConnection = new OdbcConnection(nomeConexao);
            try
            {
                this.DbConnection.Open();
                MessageBox.Show("teste");
            }
            catch (Exception ex)
            {
                MessageBox.Show("A conexão não pode ser aberta ! " + ex.Message);
                
            }

            

        } 
        #endregion
        #region fechar conexão
        public void fecharConexao()
        {
            this.DbConnection.Close();
        } 
        #endregion

        #region select
        public void select()
        {
            try
            {
                this.abrirConexao();
                OdbcCommand DbCommand = DbConnection.CreateCommand();
                DbCommand.CommandText = "SELECT matricula, nome FROM vendedor";
                OdbcDataReader DbReader = DbCommand.ExecuteReader();
                String linhas = "";
                while (DbReader.Read())
                {
                    linhas += DbReader.GetInt32(DbReader.GetOrdinal("matricula"));
                    linhas += " - ";
                    linhas += DbReader.GetString(DbReader.GetOrdinal("nome"));
                    linhas += "\n";
                }
                MessageBox.Show(linhas);
                DbReader.Close();
                DbCommand.Dispose();
                this.fecharConexao();
            }
            catch (Exception e)
            {
                
                MessageBox.Show(e.Message);
            }
            
        } 
        #endregion

        #region insert
        public void insert(Usuario usuario)
        {
           
            this.abrirConexao();
            OdbcCommand DbCommand = DbConnection.CreateCommand();
            DbCommand.CommandText = "insert into usuario (usu_cpf,usu_nome, usu_numeroCartaoCredito,usu_numeroTelefone,usu_numeroCelular, usu_dataCadastro) values ('" + usuario.cpf + "','" + usuario.nome + "','" + usuario.numeroCartaoCredito + "', '" + usuario.numeroTelefone + "', '" + usuario.numeroCelular + "', " + usuario.dataCadastro + ");";

            DbCommand.ExecuteNonQuery();
            DbCommand.Dispose();
            this.fecharConexao();
        }
        
        #endregion
        
    }
}
